from datamp import create_app, UserRole,Indicator,XAPItest
from ext import db
import json
import pymysql
from flask import Flask, jsonify, request
from flask_restful import Api, Resource, reqparse, inputs, fields, marshal, marshal_with
import pandas as pd

db2 = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="12345678", db="flask01", charset="utf8",
                              autocommit=True)
app=create_app()
with app.app_context():
    #上下文里面，测试操作OR
    # #批量添加user_role数据
    # db.session.execute(
    #     UserRole.__table__.insert(),
    #     [{"role_name": i} for i in ["教师","学生","管理者","教研员","科研人员","技术人员","应用厂商","超级管理员"]]
    # )
    # A=Indicator()
    # A.school='向明'
    # A.classname='三年级1班'
    # A.unique_number='10001'
    # A.student_name='张三'
    # A.indicator_name='劳育'
    # A.indicator_value='59'
    # db.session.add(A)
    # db.session.commit()

    # A=XAPItest()
    # A.school='向明'
    # A.classname='三年级1班'
    # A.unique_number='10001'
    # A.student_name='张三'
    # A.xapi_name='观看视频'
    # db.session.add(A)
    # db.session.commit()

# # #返回前端indicator
    #SQLAlchemy
#     temp_list = Indicator.query.with_entities(Indicator.indicator_name).distinct().all()
#     kind_list = []
#     for obj in temp_list:
#         kind_list.append(obj)
#     print(kind_list)
#     print(type(kind_list))
#     print(temp_list)
#     #[('体育',), ('德育',), ('美育',), ('智育',), ('劳育',)]
# sql语句
#     cursor = db2.cursor(cursor=pymysql.cursors.DictCursor)
#     sql = "select distinct indicator_name from indicator;"
#     cursor.execute(sql)
#     res = cursor.fetchall()
#     print(res)

#查询
#方法一
    # cursor = db2.cursor(cursor=pymysql.cursors.DictCursor)
    # sql = "select * from indicator where indicator_name='德育';"
    # cursor.execute(sql)
    # res = cursor.fetchall()
    # print('方法一',res)
    data_list=[
        {'indicator_name':'德育'},
        {'indicator_name': '美育'},
        {'indicator_name': '体育'},
    ]
    dd=[]
    for data in data_list:
        ee=data['indicator_name']
        dd.append(ee)
    print(dd)

    sql_list=dd
    cursor = db2.cursor(cursor=pymysql.cursors.DictCursor)
    # sql = "select * from indicator where indicator_name='德育';"
    # cursor.execute(sql)
    # res = cursor.fetchall()
    # print('方法一', res)

    sb=""
    sb += "where "
    sb += "indicator_name="
    for aa in sql_list:
        # 拼接单个条件多情况OR语句
        sb = sb + '\''+aa + '\''+" or indicator_name="
    sb_ = sb.rstrip(" or indicator_name= ")
    sbb = sb_ + ""
    print(sbb)
    sql = "select * from indicator %s;" % sbb
    print(sql)
    cursor.execute(sql)
    res = cursor.fetchall()
    print(res)



    # df1=pd.DataFrame(res)
    # print(df1)
    # # print("len is: ",len(df1))
    # # print("columns is: ",df1.columns)
    # # print("shape is: ", df1.shape)
    # # print("size is: ", df1.size)
    # aa = df1
    # aa['indicator_value'].apply(pd.to_numeric, errors='coerce')
    # print(aa)
    # print(type(aa['indicator_value']))
    # bb=aa['indicator_value']
    # print('bb','\n',bb)
    # cc=bb.apply(pd.to_numeric, errors='coerce')
    # print('cc','\n',cc.mean())




# #方法二
#     temp_list = Indicator.query.filter(Indicator.indicator_name == '德育').all()
#     info_fields = {
#         'id': fields.Integer,
#         'school': fields.String,
#         'classname': fields.String,
#         'unique_number': fields.String,
#         'student_name': fields.String,
#         'indicator_name': fields.String,
#         'indicator_value': fields.String,
#         'start_time':fields.DateTime,
#         'end_time':fields.DateTime,
#         'is_major':fields.Boolean,
#         'description':fields.String
#     }
#     data = marshal(temp_list, info_fields)
#     # print(data)
#     cc=[]
#     # OrderedDict转成dict
#     for u in data:
#         list_key = []
#         list_value = []
#         for key in u.keys():
#             list_key.append(key)
#         for value in u.values():
#             list_value.append(value)
#         aa = zip(list_key, list_value)
#         bb = dict(aa)
#         cc.append(bb)
#     print('方法二',cc)
#
#
# #方法三
#     temp_list = Indicator.query.filter(Indicator.indicator_name=='德育').all()
#     print('方法三',[u.to_dict() for u in temp_list])






    # kind_list = []
    # for obj in temp_list:
    #     kind_list.append(obj)
    #     print(obj)
    # print(kind_list)
    # print(type(kind_list))
    # print(json.dumps(temp_list))









